using Dapper;
using log4net;
using System;
using System.Collections.Generic;
using System.Data;

/**
*┌──────────────────────────────────────────────────────────────┐
*│　描    述：采购退货主表接口实现
*│　作    者：李宝
*│　版    本：1.0 模板代码自动生成
*│　创建时间：2019-01-05 16:55:24
*└──────────────────────────────────────────────────────────────┘
*┌──────────────────────────────────────────────────────────────┐
*│　命名空间： Usido.CRM.Repository
*│　类    名： SupplyReturnOrderRepository
*└──────────────────────────────────────────────────────────────┘
*/

using Usido.CRM.Core.DbHelper;
using Usido.CRM.Core.Extensions;
using Usido.CRM.Core.Options;
using Usido.CRM.Core.Repository;
using Usido.CRM.Core.ToolHelper;
using Usido.CRM.Dto;
using Usido.CRM.IRepository;
using Usido.CRM.Models;

namespace Usido.CRM.Repository
{
    public class SupplyReturnOrderRepository : BaseRepository<SupplyReturnOrder, int>, ISupplyReturnOrderRepository
    {
        private readonly ILog logger = LogManager.GetLogger(nameof(SupplyReturnOrderRepository));

        public SupplyReturnOrderRepository()
        {
            _dbConnection = ConnectionFactory.CreateConnection(DbOptionData.DbType, DbOptionData.ConnectionString);
        }

        public int AddSupplyReturn(SupplyReturnOrder order, List<SupplyReturnInfo> orderInfoList)
        {
            IDbTransaction transaction;
            transaction = _dbConnection.BeginTransaction();

            string sqlAddSupplyReturn = @"
INSERT INTO dbo.SupplyReturnOrder(OrderCode,CreateTime,CreateUserId,State,Remark,SupplyOrderId)
VALUES (@OrderCode,GETDATE(),@CreateUserId,@State,@Remark,@SupplyOrderId)
SELECT @@IDENTITY";

            string sqlAddSupplyReturnInfo = @"
INSERT INTO dbo.SupplyReturnInfo(ProductId,BatchNumber,Price,Quantity,OriginalSupplyInforId,OrderId,DateExpiry)
VALUES(@ProductId,@BatchNumber,@Price,@Quantity,@OriginalSupplyInforId,@OrderId,@DateExpiry)
";
            try
            {
                order.OrderCode = Tools.GetOrderCode(4);
                int orderId = _dbConnection.ExecuteScalar<int>(sqlAddSupplyReturn, order, transaction);
                if (orderId <= 0)
                {
                    transaction.Rollback();
                    return -1;
                }

                foreach (var item in orderInfoList)
                {
                    item.OrderId = orderId;
                    _dbConnection.Execute(sqlAddSupplyReturnInfo, item, transaction);
                }

                transaction.Commit();

                return orderId;
            }
            catch (Exception ex)
            {
                logger.Error("添加采购退货信息时出错", ex);
                transaction.Rollback();

                return -1;
            }
        }

        public bool DeleteSupplyReturn(int id)
        {
            string sql = @"
DELETE dbo.SupplyReturnOrder WHERE Id = @OrderId;
DELETE dbo.SupplyReturnInfo WHERE OrderId = @OrderId";

            return _dbConnection.Execute(sql, new { OrderId = id }) > 0;
        }

        public List<SupplyReturnInfoDto> GetSupplyReturnInfoList(int orderId)
        {
            string sql = @"
SELECT B.ProductName,B.ProductCode,B.Specification,B.ManufacturingEnterprise,
B.MarketingUnit, A.*, CONVERT(DECIMAL(18,2),(A.Quantity * A.Price)) AS TotalMoney,
		   C.Quantity - dbo.P_GetSupplyQuantityReturnByReturnId(A.Id) AS QuantityReturned
FROM dbo.SupplyReturnInfo AS A
INNER JOIN dbo.SysProduct AS B ON A.ProductId = B.Id
INNER JOIN dbo.SupplyInfo AS C ON A.OriginalSupplyInforId = C.Id 
WHERE A.OrderId = @OrderId ";

            var list = _dbConnection.Query<SupplyReturnInfoDto>(sql, new { OrderId = orderId });

            return list.AsList();
        }

        public List<SupplyReturnOrderDto> GetSupplyReturnList(string where = "")
        {
            string sql = @"
SELECT * FROM (
	SELECT A.*, B.UserName AS CreateUserName, C.UserName AS PutUserName
	FROM dbo.SupplyReturnOrder AS A
	INNER JOIN dbo.SysUser AS B ON A.CreateUserId = B.Id
	LEFT JOIN dbo.SysUser AS C ON A.PutUserId = C.Id
)T ";
            if (!where.IsNullOrEmpty())
            {
                sql += where;
            }

            return _dbConnection.Query<SupplyReturnOrderDto>(sql).AsList();
        }

        public bool OutStore(int orderId, int userId)
        {
            var result = _dbConnection.ExecuteScalar<int>("P_SupplyReturnShipmentStorage", new { UserId = userId, OrderId = orderId }, commandType: CommandType.StoredProcedure);

            return result == 1;
        }

        public bool UpdateSupplyReturn(SupplyReturnOrder order, List<SupplyReturnInfo> orderInfoList)
        {
            IDbTransaction transaction;
            transaction = _dbConnection.BeginTransaction();

            string sqlUpdateSupply = @"UPDATE dbo.SupplyReturnOrder SET Remark = @Remark WHERE Id = @Id";
            string sqlDeleteSupplyInfo = @"DELETE dbo.SupplyReturnInfo WHERE OrderId = @OrderId";
            string sqlUpdateSupplyInfo = @"
INSERT INTO dbo.SupplyReturnInfo(ProductId,BatchNumber,Price,Quantity,OriginalSupplyInforId,OrderId,DateExpiry)
VALUES(@ProductId,@BatchNumber,@Price,@Quantity,@OriginalSupplyInforId,@OrderId,@DateExpiry)
";
            try
            {
                _dbConnection.Execute(sqlUpdateSupply, order, transaction);
                _dbConnection.Execute(sqlDeleteSupplyInfo, new { OrderId = order.Id }, transaction);

                foreach (var item in orderInfoList)
                {
                    _dbConnection.Execute(sqlUpdateSupplyInfo, item, transaction);
                }

                transaction.Commit();

                return true;
            }
            catch (Exception ex)
            {
                logger.Error("修改采购退货信息时出错", ex);
                transaction.Rollback();

                return false;
            }
        }
    }
}